package dao;

import contractmessageservice.ContractMessage;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Unmarshaller;
import javax.xml.transform.stream.StreamSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public class DerbyDao {

    private JdbcTemplate template;

    public DerbyDao() {
        // Initialize the datasource, could /should be done of Spring
        // configuration
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.apache.derby.jdbc.EmbeddedDriver");
        dataSource.setUrl("jdbc:mysql://www.freesql.org:3306/a72084");
        dataSource.setUsername("a72084");
        dataSource.setPassword("esipass");
        template = new JdbcTemplate(dataSource);
    }

    public void createContract(int supplier, int client, Date start, Date end, String area, String facility, String settlementMethod) {
        template.update("INSERT INTO CONTRACT (SUPPLIER_ID, CLIENT_ID, START_DATE, END_DATE, AREA, FACILITY, SETTLEMENT_METHOD) VALUES(?,?,?,?,?,?,?)",
                new Object[]{supplier, client, start, end, area, facility, settlementMethod});
    }

    public void createMeasurement(Date date, long contractId, float measurement, String measurer) {
        template.update("INSERT INTO MEASUREMENT(DATE, CONTRACT_ID, MEASUREMENT, MEASURER) VALUES(?,?,?,?)",
                new Object[]{date, contractId, measurement, measurer});
    }

    public int findRegNumByContract(int id) {
        try {
            return template.queryForInt("SELECT REG_NR from CLIENT cli, CONTRACT con where con.id=? and cli.id=con.CLIENT_ID", id);
        } catch (Exception e) {
            return 0;
        }
    }

    public List<ContractMessage> getMessages() {
        List<ContractMessage> cms = null;
        try {
            List<String> body = template.queryForList("SELECT BODY FROM RECIEVED_MESSAGES", String.class);
            JAXBContext jaxb = JAXBContext.newInstance(ContractMessage.class);
            Unmarshaller um = jaxb.createUnmarshaller();
            cms = new ArrayList<ContractMessage>();
            for (String s : body) {
                cms.add((ContractMessage) um.unmarshal(new StreamSource(new StringReader(s))));
            }
        } catch (JAXBException ex) {
            Logger.getLogger(DerbyDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return cms;
    }

    public ContractMessage getLastMessage() {
        try {
            String body = template.queryForObject("SELECT BODY FROM RECIEVED_MESSAGES ORDER BY ID DESC LIMIT 1", String.class);
            JAXBContext jaxb = JAXBContext.newInstance(ContractMessage.class);
            Unmarshaller um = jaxb.createUnmarshaller();
            return (ContractMessage) um.unmarshal(new StreamSource(new StringReader(body)));
        } catch (JAXBException ex) {
            Logger.getLogger(DerbyDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public void insertMessage(String body) {
        String sql = "insert into RECIEVED_MESSAGES(body) values (?)";
        new JdbcTemplate().update(sql, body);
    }

    public int getComposedMessageId() {
        try {
            return template.queryForInt("SELECT ID FROM COMPOSED_MESSAGES ORDER BY ID DESC LIMIT 1");
        } catch (Exception e) {
            return 0;
        }

    }

    public int insertComposedMessage(final String body) {
        final String sql = "insert into COMPOSED_MESSAGES(body) values (?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        template.update(
                new PreparedStatementCreator() {

                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps =
                                connection.prepareStatement(sql, new String[]{"id"});
                        ps.setString(1, body);
                        return ps;
                    }
                },
                keyHolder);

        return keyHolder.getKey().intValue();
    }
}